# -*- coding:utf-8 -*-
import cx_Oracle
import configparser
import logging


class Oracle(object):
    def __init__(self):
        cfg = configparser.ConfigParser()
        cfg.read("config.ini")
        self.name = cfg.get("db", "user")
        self.password = cfg.get("db", "password")
        self.url = cfg.get("db", "url")
        self.cursor = None
        self.isClosed = True
        self.conn = None

    # oracle connect
    def open(self):
        try:
            self.conn = cx_Oracle.connect(self.name, self.password, self.url)
            self.cursor = cx_Oracle.Cursor(self.conn)
            self.isClosed = False
        except Exception as e:
            logging.error(
                "Oracle connect error![name=%s,passowrd=%s,url=%s]" % (self.name, self.password, self.url))
            self.cursor = None
            self.isClosed = True
        return self.cursor

    # oracle connected or not
    def isOpen(self):
        if self.cursor is not None:
            return True
        else:
            return False

    # close oracle connect
    def close(self):
        self.cursor.close()
        self.conn.close()
        self.isClosed = True

    # execute SQL
    # return:
    #       None: error
    #       empty list: 0 result
    #       normal list: normal result, a list of tuples
    def executeSQL(self, sql):
        if not self.isOpen():
            conn = self.open()
            if not self.isOpen():
                return None
        else:
            conn = self.cursor
        try:
            r = conn.execute(sql)
            sqlRes = r.fetchall()
        except Exception as e:
            logging.error("Excute sql error![MSG=%s,SQL=%s]" % (str(e), sql))
            return None
        return sqlRes

    def insert(self, sql):
        self.execute(sql)

    def delete(self, sql):
        self.execute(sql)

    def executeSQLForOne(self, sql):
        if not self.isOpen():
            conn = self.open()
            if not self.isOpen():
                return None
        else:
            conn = self.cursor
        try:
            r = conn.execute(sql)
            sqlRes = r.fetchone()
        except Exception as e:
            logging.error("Excute sql error![MSG=%s,SQL=%s]" % (str(e), sql))
            return None
        return sqlRes

    def commit(self):
        self.conn.commit()

    def execute(self,sql):
        if not self.isOpen():
            conn = self.open()
            if not self.isOpen():
                return None
        else:
            conn = self.cursor
        try:
            r = conn.execute(sql)
        except Exception as e:
            logging.error("Excute sql error![MSG=%s,SQL=%s]" % (str(e), sql))
            return None
